package com.example.service;

import com.example.entity.User;
import org.apache.poi.poifs.crypt.EncryptionInfo;
import org.apache.poi.poifs.crypt.EncryptionMode;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.usermodel.XSSFColor;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Service;

import java.io.FileOutputStream;
import java.io.IOException;
import java.io.OutputStream;
import java.security.GeneralSecurityException;
import java.util.List;

/**
 * Author wangJinLong
 * Date 2025/11/20 10:28
 **/
@Service
public class ExcelService {

    /**
     * 查看每种颜色
     * @param filepath
     * @throws IOException
     */
    public void colorExample(String filepath) throws IOException {
        XSSFWorkbook workbook = new XSSFWorkbook();
        XSSFSheet sheet = workbook.createSheet("Sheet1");

        int rowIdx = 0;
        for (IndexedColors color : IndexedColors.values()) {
            if (color.getIndex() >= 65) continue; // 跳过无效或特殊值
            Row row = sheet.createRow(rowIdx++);
            Cell cell = row.createCell(0);
            cell.setCellValue(color.name());

            CellStyle style = workbook.createCellStyle();
            style.setFillForegroundColor(color.getIndex());
            style.setFillPattern(FillPatternType.SOLID_FOREGROUND);
            cell.setCellStyle(style);
        }

        // 写入文件
        try (FileOutputStream fos = new FileOutputStream(filepath)) {
            workbook.write(fos);
        }

    }

    /**
     * 创建加密excel
     * @param headers
     * @param users
     * @param filepath
     * @throws Exception
     */
    public void excelWithPassword(String[] headers, List<User> users, String filepath) throws Exception {
        // 创建一个新的工作簿
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("加密示例");

        CellStyle centerStyle = getCellCenterStyle(workbook);

        // 创建表头
        Row headerRow = sheet.createRow(0);
        headerRow.setHeightInPoints(24.0f); // 表头行高 24 磅
        for (int i = 0; i < headers.length; i++) {
            Cell cell = headerRow.createCell(i);
            cell.setCellValue(headers[i]);
            cell.setCellStyle(centerStyle);
        }

        // 4. 写入数据
        int rowNum = 1;
        for (User user : users) {
            Row row = sheet.createRow(rowNum++);
            Cell cell0 = row.createCell(0);
            cell0.setCellValue(user.getName());
            cell0.setCellStyle(centerStyle);

            Cell cell1 = row.createCell(1);
            cell1.setCellValue(user.getAge());
            cell1.setCellStyle(centerStyle);

            Cell cell2 = row.createCell(2);
            cell2.setCellValue(user.getEmail());
            cell2.setCellStyle(centerStyle);
        }

        // 自动调整列宽（必须在所有数据写入后调用）
        for (int i = 0; i < headers.length; i++) {
            sheet.autoSizeColumn(i);
        }

        // 将未加密的工作簿写入到输出流中
        try (POIFSFileSystem fs = new POIFSFileSystem()) {
            // 创建EncryptionInfo对象，这里我们使用标准的加密模式
            EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);

            // 创建Encryptor实例
            org.apache.poi.poifs.crypt.Encryptor enc = info.getEncryptor();
            enc.confirmPassword("your_password"); // 设置打开文件的密码

            // 写入内容到Encryptor的输出流中
            try (OutputStream os = enc.getDataStream(fs)) {
                workbook.write(os); // 将工作簿的内容写入到加密输出流中
            }

            // 最后将加密后的文件写入到磁盘
            try (FileOutputStream fos = new FileOutputStream(filepath)) {
                fs.writeFilesystem(fos); // 将整个加密的文件系统写入到文件输出流
            }
        }

        System.out.println("加密完成.");
    }

    /**
     * 创建excel
     * @param headers
     * @param users
     * @throws IOException
     */
    public void excel2(String[] headers, List<User> users, String filepath) throws IOException {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("用户表");

            // 设置字体以确保一致性
            Font font = workbook.createFont();
            font.setFontName("宋体");
            font.setFontHeightInPoints((short) 12);
            font.setColor(IndexedColors.BLACK.getIndex());

            // 创建居中样式
            CellStyle centerStyle = workbook.createCellStyle();
            centerStyle.setAlignment(HorizontalAlignment.CENTER);   // 水平居中
            centerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
            centerStyle.setFont(font);

            // 创建表头
            Row headerRow = sheet.createRow(0);
            headerRow.setHeightInPoints(24.0f); // 表头行高 24 磅

            for (int i = 0; i < headers.length; i++) {
                Cell cell = headerRow.createCell(i);
                cell.setCellValue(headers[i]);
                cell.setCellStyle(centerStyle);
            }

            // 4. 写入数据
            int rowNum = 1;
            for (User user : users) {
                Row row = sheet.createRow(rowNum++);
                Cell cell0 = row.createCell(0);
                cell0.setCellValue(user.getName());
                cell0.setCellStyle(centerStyle);

                Cell cell1 = row.createCell(1);
                cell1.setCellValue(user.getAge());
                cell1.setCellStyle(centerStyle);

                Cell cell2 = row.createCell(2);
                cell2.setCellValue(user.getEmail());
                cell2.setCellStyle(centerStyle);
            }

            // 自动调整列宽（必须在所有数据写入后调用）
            for (int i = 0; i < headers.length; i++) {
                sheet.autoSizeColumn(i);
                // 确保每列至少有 10 个字符的宽度
                if (sheet.getColumnWidth(i) < 10 * 256) {
                    sheet.setColumnWidth(i, 10 * 256);
                }
            }

            // 写入文件
            try (FileOutputStream fos = new FileOutputStream(filepath)) {
                workbook.write(fos);
            }
        }
    }

    /**
     * 加密
     * @throws IOException
     * @throws GeneralSecurityException
     */
    public void jiaMiExcel() throws IOException, GeneralSecurityException {
        // 创建一个新的工作簿
        Workbook wb = new XSSFWorkbook();
        Sheet sheet = wb.createSheet("加密示例");

        // 创建一些示例行数据
        Row row = sheet.createRow(0);
        Cell cell = row.createCell(0);
        cell.setCellValue("这是一个加密的Excel文件示例.");

        // 将未加密的工作簿写入到输出流中
        try (POIFSFileSystem fs = new POIFSFileSystem()) {
            // 创建EncryptionInfo对象，这里我们使用标准的加密模式
            EncryptionInfo info = new EncryptionInfo(EncryptionMode.standard);

            // 创建Encryptor实例
            org.apache.poi.poifs.crypt.Encryptor enc = info.getEncryptor();
            enc.confirmPassword("your_password"); // 设置打开文件的密码

            // 写入内容到Encryptor的输出流中
            try (OutputStream os = enc.getDataStream(fs)) {
                wb.write(os); // 将工作簿的内容写入到加密输出流中
            }

            // 最后将加密后的文件写入到磁盘
            try (FileOutputStream fos = new FileOutputStream("D://加密文件.xlsx")) {
                fs.writeFilesystem(fos); // 将整个加密的文件系统写入到文件输出流
            }
        }

        System.out.println("加密完成.");
    }

    private CellStyle getCellCenterStyle(Workbook workbook) {
        // 设置字体以确保一致性
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);

        // 创建居中样式
        CellStyle centerStyle = workbook.createCellStyle();
        centerStyle.setAlignment(HorizontalAlignment.CENTER);   // 水平居中
        centerStyle.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        centerStyle.setFont(font);
        return centerStyle;
    }

    private CellStyle createCellBackground(Workbook workbook, byte[] rgb) {
        CellStyle style = workbook.createCellStyle();

        // 设置字体以确保一致性
        Font font = workbook.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short) 12);

        // 设置自定义颜色，使用RGB值
        XSSFColor color = new XSSFColor(rgb, null);
        style.setFillForegroundColor(color);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 居中
        style.setAlignment(HorizontalAlignment.CENTER);   // 水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中
        style.setFont(font);

        // 设置单元格的所有四边框样式
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK1.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK1.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK1.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK1.getIndex());

        return style;
    }

    /**
     * 设置单元格样式
     * @param style
     * @param colorIndex
     * @return
     */
    private CellStyle createCellBackground2(CellStyle style, short colorIndex) {
        style.setFillForegroundColor(colorIndex);
        style.setFillPattern(FillPatternType.SOLID_FOREGROUND);

        // 居中
        style.setAlignment(HorizontalAlignment.CENTER);   // 水平居中
        style.setVerticalAlignment(VerticalAlignment.CENTER); // 垂直居中

        // 设置单元格的所有四边框样式
        style.setBorderTop(BorderStyle.THIN);
        style.setTopBorderColor(IndexedColors.BLACK1.getIndex());
        style.setBorderBottom(BorderStyle.THIN);
        style.setBottomBorderColor(IndexedColors.BLACK1.getIndex());
        style.setBorderLeft(BorderStyle.THIN);
        style.setLeftBorderColor(IndexedColors.BLACK1.getIndex());
        style.setBorderRight(BorderStyle.THIN);
        style.setRightBorderColor(IndexedColors.BLACK1.getIndex());

        return style;
    }

    public void createListExcel(String filepath) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("资源列表");
        int rowNum = 0;

        // 创建不同样式的 CellColor
        byte[] serverColor = new byte[]{(byte) 112, (byte) 173, (byte) 71};
        byte[] subTypeColor = new byte[]{(byte) 255, (byte) 192, (byte) 0};
        byte[] headerColor = new byte[]{(byte) 214, (byte) 220, (byte) 228};
        byte[] cellColor = new byte[]{(byte) 204, (byte) 232, (byte) 207};

        // 创建不同样式的 CellStyle
        CellStyle serverStyle = createCellBackground(workbook, serverColor);
        CellStyle subTypeStyle = createCellBackground(workbook, subTypeColor);
        CellStyle headerStyle = createCellBackground(workbook, headerColor);
        CellStyle cellStyle = createCellBackground(workbook, cellColor);

        // ==================== 第一个表格：服务器 - 申请新服务器 ====================
        // 第一行
        Row row0 = sheet.createRow(rowNum);
        Cell cellA0 = row0.createCell(0);
        cellA0.setCellValue("服务器");
        cellA0.setCellStyle(serverStyle); // 橙色背景

        Cell cellB0 = row0.createCell(1);
        cellB0.setCellValue("申请新服务器");
        cellB0.setCellStyle(subTypeStyle); // 黄色背景
        
        // 第二行 表格headers
        Row row1 = sheet.createRow(1);
        Cell cellA1 = row1.createCell(0);
        cellA1.setCellValue("*使用场景");
        cellA1.setCellStyle(headerStyle); // 蓝色背景

        Cell cellB1 = row1.createCell(1);
        cellB1.setCellValue("*资源评估依据");
        cellB1.setCellStyle(headerStyle);

        Cell cellC1 = row1.createCell(2);
        cellC1.setCellValue("*数据中心");
        cellC1.setCellStyle(headerStyle);

        // 写入数据行
        Row dataRow = sheet.createRow(2);
        dataRow.createCell(0).setCellValue("业务场景1");
        dataRow.createCell(1).setCellValue("资源评估依据1");
        dataRow.createCell(2).setCellValue("枣庄");
        dataRow.getCell(0).setCellStyle(cellStyle);
        dataRow.getCell(1).setCellStyle(cellStyle);
        dataRow.getCell(2).setCellStyle(cellStyle);

        // ==================== 第二个表格：回收服务器或特权账号 ====================
        Row titleRow2 = sheet.createRow(4);
        Cell cellA6 = titleRow2.createCell(0);
        cellA6.setCellValue("服务器");
        cellA6.setCellStyle(serverStyle);

        Cell cellB6 = titleRow2.createCell(1);
        cellB6.setCellValue("回收服务器或特权账号");
        cellB6.setCellStyle(subTypeStyle);

        // 表头
        Row headerRow2 = sheet.createRow(5);
        Cell cellA7 = headerRow2.createCell(0);
        cellA7.setCellValue("*使用场景");
        cellA7.setCellStyle(headerStyle);

        Cell cellB7 = headerRow2.createCell(1);
        cellB7.setCellValue("*资源评估依据");
        cellB7.setCellStyle(headerStyle);

        Cell cellC7 = headerRow2.createCell(2);
        cellC7.setCellValue("*数据中心");
        cellC7.setCellStyle(headerStyle);

        // 数据行
        Row dataRow2 = sheet.createRow(6);
        dataRow2.createCell(0).setCellValue("业务需要1");
        dataRow2.createCell(1).setCellValue("资源评估依据1");
        dataRow2.createCell(2).setCellValue("枣庄");
        dataRow2.getCell(0).setCellStyle(cellStyle);
        dataRow2.getCell(1).setCellStyle(cellStyle);
        dataRow2.getCell(2).setCellStyle(cellStyle);

        // 先设置最小宽度
        for (int i = 0; i < 3; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        }

        // ==================== 写入文件 ====================
        try (FileOutputStream fos = new FileOutputStream(filepath)) {
            workbook.write(fos);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            System.out.println("excel已生成");
        }
    }

    public void createExcelUseColor(String filepath) {
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet("资源列表");
        int rowNum = 0;

        // 创建不同样式的 CellColor
        short serverColor = IndexedColors.LIGHT_ORANGE.getIndex();
        short subTypeColor = IndexedColors.WHITE.getIndex();
        short headerColor = IndexedColors.PALE_BLUE.getIndex();
        short cellColor = IndexedColors.WHITE.getIndex();

        // 创建不同样式的 CellStyle
        CellStyle serverStyle = createCellBackground2(workbook.createCellStyle(), serverColor);
        CellStyle subTypeStyle = createCellBackground2(workbook.createCellStyle(), subTypeColor);
        CellStyle headerStyle = createCellBackground2(workbook.createCellStyle(), headerColor);
        CellStyle cellStyle = createCellBackground2(workbook.createCellStyle(), cellColor);

        // ==================== 第一个表格：服务器 - 申请新服务器 ====================
        // 第一行
        Row row0 = sheet.createRow(rowNum);
        Cell cellA0 = row0.createCell(0);
        cellA0.setCellValue("服务器");
        cellA0.setCellStyle(serverStyle); // 橙色背景

        Cell cellB0 = row0.createCell(1);
        cellB0.setCellValue("申请新服务器");
        cellB0.setCellStyle(subTypeStyle); // 黄色背景

        // 第二行 表格headers
        Row row1 = sheet.createRow(1);
        Cell cellA1 = row1.createCell(0);
        cellA1.setCellValue("*使用场景");
        cellA1.setCellStyle(headerStyle); // 蓝色背景

        Cell cellB1 = row1.createCell(1);
        cellB1.setCellValue("*资源评估依据");
        cellB1.setCellStyle(headerStyle);

        Cell cellC1 = row1.createCell(2);
        cellC1.setCellValue("*数据中心");
        cellC1.setCellStyle(headerStyle);

        // 写入数据行
        Row dataRow = sheet.createRow(2);
        dataRow.createCell(0).setCellValue("业务场景1");
        dataRow.createCell(1).setCellValue("资源评估依据1");
        dataRow.createCell(2).setCellValue("枣庄");
        dataRow.getCell(0).setCellStyle(cellStyle);
        dataRow.getCell(1).setCellStyle(cellStyle);
        dataRow.getCell(2).setCellStyle(cellStyle);

        // ==================== 第二个表格：回收服务器或特权账号 ====================
        Row titleRow2 = sheet.createRow(4);
        Cell cellA6 = titleRow2.createCell(0);
        cellA6.setCellValue("服务器");
        cellA6.setCellStyle(serverStyle);

        Cell cellB6 = titleRow2.createCell(1);
        cellB6.setCellValue("回收服务器或特权账号");
        cellB6.setCellStyle(subTypeStyle);

        // 表头
        Row headerRow2 = sheet.createRow(5);
        Cell cellA7 = headerRow2.createCell(0);
        cellA7.setCellValue("*使用场景");
        cellA7.setCellStyle(headerStyle);

        Cell cellB7 = headerRow2.createCell(1);
        cellB7.setCellValue("*资源评估依据");
        cellB7.setCellStyle(headerStyle);

        Cell cellC7 = headerRow2.createCell(2);
        cellC7.setCellValue("*数据中心");
        cellC7.setCellStyle(headerStyle);

        // 数据行
        Row dataRow2 = sheet.createRow(6);
        dataRow2.createCell(0).setCellValue("业务需要1");
        dataRow2.createCell(1).setCellValue("资源评估依据1");
        dataRow2.createCell(2).setCellValue("枣庄");
        dataRow2.getCell(0).setCellStyle(cellStyle);
        dataRow2.getCell(1).setCellStyle(cellStyle);
        dataRow2.getCell(2).setCellStyle(cellStyle);

        // 先设置最小宽度
        for (int i = 0; i < 3; i++) {
            sheet.setColumnWidth(i, 20 * 256);
        }

        // ==================== 写入文件 ====================
        try (FileOutputStream fos = new FileOutputStream(filepath)) {
            workbook.write(fos);
        } catch (IOException e) {
            throw new RuntimeException(e);
        } finally {
            System.out.println("excel已生成");
        }
    }

    public void mergeCell(String filepath) {
        try (Workbook workbook = new XSSFWorkbook()) {
            Sheet sheet = workbook.createSheet("Sheet1");

            // 创建不同样式的 CellColor
            byte[] serverColor = new byte[]{(byte) 112, (byte) 173, (byte) 71};
            byte[] subTypeColor = new byte[]{(byte) 255, (byte) 192, (byte) 0};
            byte[] headerColor = new byte[]{(byte) 214, (byte) 220, (byte) 228};
            byte[] cellColor = new byte[]{(byte) 204, (byte) 232, (byte) 207};

            // 创建不同样式的 CellStyle
            CellStyle serverStyle = createCellBackground(workbook, serverColor);
            CellStyle subTypeStyle = createCellBackground(workbook, subTypeColor);
            CellStyle headerStyle = createCellBackground(workbook, headerColor);
            CellStyle cellStyle = createCellBackground(workbook, cellColor);

            // ==================== 表头 ====================
            Row headerRow = sheet.createRow(0);
            headerRow.createCell(0).setCellValue("服务器");
            headerRow.createCell(1).setCellValue("申请新服务器");

            headerRow.getCell(0).setCellStyle(serverStyle);
            headerRow.getCell(1).setCellStyle(subTypeStyle);

            Row subHeaderRow = sheet.createRow(1);
            subHeaderRow.createCell(0).setCellValue("使用场景");
            subHeaderRow.createCell(1).setCellValue("资源评估依据");
            subHeaderRow.createCell(2).setCellValue("数据中心");
            subHeaderRow.createCell(3).setCellValue("台数");
            subHeaderRow.createCell(4).setCellValue("服务器IP");
            subHeaderRow.createCell(5).setCellValue("端口");
            subHeaderRow.createCell(6).setCellValue("账号");
            subHeaderRow.createCell(7).setCellValue("密码");

            // 设置表头样式
            for (int i = 0; i < 8; i++) {
                subHeaderRow.getCell(i).setCellStyle(headerStyle);
            }

            // ==================== 数据区 ====================
            // 主项：业务场景1
            String scenario = "业务场景1";
            String basis = "资源评估依据1";
            String dataCenter = "枣庄";
            int amount = 3;

            // 假设需要3个IP
            String[] ips = {"127.0.0.1", "127.0.0.2", "127.0.0.3"};
            String[] ports = {"22", "22", "22"};
            String[] accounts = {"user1", "user1", "user1"};
            String[] passwords = {"123", "123", "123"};

            // 开始行号
            int startRow = 2;

            // 写入主项（A列和B列）
            for (int i = 0; i < ips.length; i++) {
                Row dataRow = sheet.createRow(startRow + i);
                Cell aCell = dataRow.createCell(0);
                aCell.setCellValue(scenario); // 每行都写入
                aCell.setCellStyle(cellStyle);

                Cell bCell = dataRow.createCell(1);
                bCell.setCellValue(basis); // 每行都写入
                bCell.setCellStyle(cellStyle);

                Cell cCell = dataRow.createCell(2);
                cCell.setCellValue(dataCenter);
                cCell.setCellStyle(cellStyle);

                Cell dCell = dataRow.createCell(3);
                dCell.setCellValue(amount);
                dCell.setCellStyle(cellStyle);

                // 子项（E~H列）
                dataRow.createCell(4).setCellValue(ips[i]);
                dataRow.createCell(5).setCellValue(ports[i]);
                dataRow.createCell(6).setCellValue(accounts[i]);
                dataRow.createCell(7).setCellValue(passwords[i]);

                // 设置子项样式
                for (int j = 4; j < 8; j++) {
                    dataRow.getCell(j).setCellStyle(cellStyle);
                }
            }

            // ==================== 合并单元格 ====================
            // 合并 A3:A5（业务场景1）
            sheet.addMergedRegion(new CellRangeAddress(2, 4, 0, 0));

            // 合并 B3:B5（资源评估依据1）
            sheet.addMergedRegion(new CellRangeAddress(2, 4, 1, 1));

            // 合并 C3:C5（枣庄）
            sheet.addMergedRegion(new CellRangeAddress(2, 4, 2, 2));

            // 合并 D3:D5（3）
            sheet.addMergedRegion(new CellRangeAddress(2, 4, 3, 3));

            // ==================== 自动调整列宽 ====================
            for (int i = 0; i < 8; i++) {
                sheet.setColumnWidth(i, 20 * 256);
            }

            // ==================== 写入文件 ====================
            try (FileOutputStream fos = new FileOutputStream(filepath)) {
                workbook.write(fos);
            }
        } catch (IOException e) {
            throw new RuntimeException(e);
        }
    }
}
